using Microsoft.EntityFrameworkCore;
using TerritoryGame.Domain.Entities.App;
using System.Text.Json;

namespace TerritoryGame.Infrastructure.Data;

public class TerritoryGameDbContext : DbContext
{
    public TerritoryGameDbContext(DbContextOptions<TerritoryGameDbContext> options) : base(options)
    {
    }

    // DbSet 配置
    public DbSet<User> Users { get; set; }
    public DbSet<GameRoom> GameRooms { get; set; }
    public DbSet<Player> Players { get; set; }
    public DbSet<PaintAction> PaintActions { get; set; }
    public DbSet<GameRecord> GameRecords { get; set; }
    public DbSet<PlayerStats> PlayerStats { get; set; }
    public DbSet<LeaderboardEntry> LeaderboardEntries { get; set; }
    public DbSet<UserLevel> UserLevels { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // 配置表名和字段命名规则（下划线）
        ConfigureUsers(modelBuilder);
        ConfigureGameRooms(modelBuilder);
        ConfigurePlayers(modelBuilder);
        ConfigurePaintActions(modelBuilder);
        ConfigureGameRecords(modelBuilder);
        ConfigurePlayerStats(modelBuilder);
        ConfigureLeaderboardEntries(modelBuilder);
        ConfigureUserLevels(modelBuilder);
    }

    private void ConfigureUsers(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.ToTable("users");
            entity.HasKey(e => e.Id);
            
            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.Username).HasColumnName("username").HasMaxLength(50).IsRequired();
            entity.Property(e => e.Password).HasColumnName("password").HasMaxLength(255).IsRequired();
            entity.Property(e => e.Salt).HasColumnName("salt").HasMaxLength(60).IsRequired();
            entity.Property(e => e.Email).HasColumnName("email").HasMaxLength(100).IsRequired();
            entity.Property(e => e.Phone).HasColumnName("phone").HasMaxLength(20).IsRequired();
            entity.Property(e => e.Avatar).HasColumnName("avatar").HasMaxLength(500);

            // 新增账号状态与安全相关字段映射（下划线命名）
            entity.Property(e => e.AccountStatus).HasColumnName("account_status").HasDefaultValue(0);
            entity.Property(e => e.FailedLoginAttempts).HasColumnName("failed_login_attempts").HasDefaultValue(0);
            entity.Property(e => e.LockedUntil).HasColumnName("locked_until");
            entity.Property(e => e.LastFailedLogin).HasColumnName("last_failed_login");
            entity.Property(e => e.LastActivityTime).HasColumnName("last_activity_time");

            entity.Property(e => e.LastLoginTime).HasColumnName("last_login_time");
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at");
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at");

            // 索引
            entity.HasIndex(e => e.Username).IsUnique();
            entity.HasIndex(e => e.Email).IsUnique();
            // 移除Phone的唯一索引，因为手机号可能为空
            entity.HasIndex(e => e.Phone);
        });
    }

    private void ConfigureGameRooms(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GameRoom>(entity =>
        {
            entity.ToTable("game_rooms");
            entity.HasKey(e => e.Id);

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.RoomName).HasColumnName("room_name").HasMaxLength(100).IsRequired();
            entity.Property(e => e.MaxPlayers).HasColumnName("max_players");
            entity.Property(e => e.GameDuration).HasColumnName("game_duration");
            entity.Property(e => e.Status).HasColumnName("status");
            entity.Property(e => e.StartTime).HasColumnName("start_time");
            entity.Property(e => e.EndTime).HasColumnName("end_time");
            entity.Property(e => e.CanvasData).HasColumnName("canvas_data");
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at");
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at");

            entity.Ignore(e => e.Creator);
            entity.Ignore(e => e.Players);
        });
    }

    private void ConfigurePlayers(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Player>(entity =>
        {
            entity.ToTable("players");
            entity.HasKey(e => e.Id);

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.Username).HasColumnName("username").HasMaxLength(50).IsRequired();
            entity.Property(e => e.Color).HasColumnName("color").HasMaxLength(20);
            entity.Property(e => e.AreaCount).HasColumnName("area_count");
            entity.Property(e => e.ConnectionId).HasColumnName("connection_id").HasMaxLength(100);
            entity.Property(e => e.IsOnline).HasColumnName("is_online").HasDefaultValue(true);
            entity.Property(e => e.LastSeen).HasColumnName("last_seen");
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at");
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at");
        });
    }

    private void ConfigurePaintActions(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PaintAction>(entity =>
        {
            entity.ToTable("paint_actions");
            entity.HasKey(e => e.Id);

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.RoomId).HasColumnName("room_id");
            entity.Property(e => e.PlayerId).HasColumnName("player_id");
            entity.Property(e => e.X).HasColumnName("x");
            entity.Property(e => e.Y).HasColumnName("y");
            entity.Property(e => e.Color).HasColumnName("color").HasMaxLength(20);
            entity.Property(e => e.Tool).HasColumnName("tool").HasMaxLength(20);
            entity.Property(e => e.Timestamp).HasColumnName("timestamp");
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at");
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at");

            entity.HasIndex(e => e.RoomId);
            entity.HasIndex(e => e.PlayerId);

            entity.HasOne(e => e.Room)
                .WithMany()
                .HasForeignKey(e => e.RoomId)
                .OnDelete(DeleteBehavior.Cascade);

            entity.HasOne(e => e.Player)
                .WithMany(p => p.PaintActions)
                .HasForeignKey(e => e.PlayerId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }

    private void ConfigureGameRecords(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GameRecord>(entity =>
        {
            entity.ToTable("game_records");
            entity.HasKey(e => e.Id);

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.RoomId).HasColumnName("room_id");
            entity.Property(e => e.RoomName).HasColumnName("room_name").HasMaxLength(100);
            entity.Property(e => e.WinnerId).HasColumnName("winner_id");
            entity.Property(e => e.WinnerUsername).HasColumnName("winner_username").HasMaxLength(50);
            entity.Property(e => e.PlayerCount).HasColumnName("player_count");
            entity.Property(e => e.Duration).HasColumnName("duration");
            entity.Property(e => e.StartedAt).HasColumnName("started_at");
            entity.Property(e => e.EndedAt).HasColumnName("ended_at");
            entity.Property(e => e.Status).HasColumnName("status");
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at");
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at");

            entity.HasIndex(e => e.RoomId);
            entity.HasIndex(e => e.WinnerId);

            entity.Ignore(e => e.Room);
            entity.Ignore(e => e.Winner);
            entity.Ignore(e => e.PlayerResults);
        });
    }

    private void ConfigurePlayerStats(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PlayerStats>(entity =>
        {
            entity.ToTable("player_stats");
            entity.HasKey(e => e.Id);

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.PlayerId).HasColumnName("player_id");
            entity.Property(e => e.Username).HasColumnName("username").HasMaxLength(50);
            entity.Property(e => e.TotalGamesPlayed).HasColumnName("total_games_played");
            entity.Property(e => e.TotalGamesWon).HasColumnName("total_games_won");
            entity.Property(e => e.TotalAreaPainted).HasColumnName("total_area_painted");
            entity.Property(e => e.AverageAreaPerGame).HasColumnName("average_area_per_game");
            entity.Property(e => e.TotalPlayTime).HasColumnName("total_play_time");
            entity.Property(e => e.FirstGameDate).HasColumnName("first_game_date");
            entity.Property(e => e.LastGameDate).HasColumnName("last_game_date");
            entity.Property(e => e.CurrentStreak).HasColumnName("current_streak");
            entity.Property(e => e.BestStreak).HasColumnName("best_streak");
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at");
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at");

            entity.HasIndex(e => e.PlayerId);

            entity.Ignore(e => e.WinRate);

            entity.HasOne<Player>()
                .WithMany()
                .HasForeignKey(e => e.PlayerId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }

    private void ConfigureLeaderboardEntries(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<LeaderboardEntry>(entity =>
        {
            entity.ToTable("leaderboard_entries");
            entity.HasKey(e => e.Id);

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.PlayerId).HasColumnName("player_id");
            entity.Property(e => e.Username).HasColumnName("username").HasMaxLength(50);
            entity.Property(e => e.Rank).HasColumnName("rank");
            entity.Property(e => e.TotalGamesWon).HasColumnName("total_games_won");
            entity.Property(e => e.TotalAreaPainted).HasColumnName("total_area_painted");
            entity.Property(e => e.WinRate).HasColumnName("win_rate");
            entity.Property(e => e.CurrentStreak).HasColumnName("current_streak");
            entity.Property(e => e.BestStreak).HasColumnName("best_streak");
            entity.Property(e => e.LastGameDate).HasColumnName("last_game_date");
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at");
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at");

            entity.HasIndex(e => e.PlayerId);

            entity.HasOne<Player>()
                .WithMany()
                .HasForeignKey(e => e.PlayerId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }

    private void ConfigureUserLevels(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserLevel>(entity =>
        {
            entity.ToTable("user_levels");
            // 使用自增的主键 id；同时对 user_id 建唯一索引，确保一人一条记录
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => e.UserId).IsUnique();
            entity.Property(e => e.Id).HasColumnName("id");
            
            entity.Property(e => e.UserId).HasColumnName("user_id").IsRequired();
            entity.Property(e => e.CurrentLevel).HasColumnName("current_level").IsRequired().HasDefaultValue(1);
            entity.Property(e => e.CurrentExperience).HasColumnName("current_experience").IsRequired().HasDefaultValue(0);
            entity.Property(e => e.TotalExperience).HasColumnName("total_experience").IsRequired().HasDefaultValue(0);
            entity.Property(e => e.CurrentTitle).HasColumnName("current_title").IsRequired().HasMaxLength(50).HasDefaultValue("墨痕初绽");
            entity.Property(e => e.UnlockedTitles)
                .HasColumnName("unlocked_titles")
                .HasColumnType("jsonb")
                .HasConversion(
                    v => JsonSerializer.Serialize(v ?? new List<string>(), (JsonSerializerOptions)null),
                    v => string.IsNullOrEmpty(v)
                        ? new List<string>()
                        : (JsonSerializer.Deserialize<List<string>>(v, (JsonSerializerOptions)null) ?? new List<string>())
                );
            entity.Property(e => e.LastLevelUpTime).HasColumnName("last_level_up_time").IsRequired();
            entity.Property(e => e.IsActive).HasColumnName("is_active").HasDefaultValue(true);
            entity.Property(e => e.IsDeleted).HasColumnName("is_deleted").HasDefaultValue(false);
            entity.Property(e => e.CreatedAt).HasColumnName("created_at").HasDefaultValue(DateTime.UtcNow);
            entity.Property(e => e.UpdatedAt).HasColumnName("updated_at").HasDefaultValue(DateTime.UtcNow);
            
            entity.HasOne(e => e.User)
                .WithOne()
                .HasForeignKey<UserLevel>(e => e.UserId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }
}
